﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Threading.Tasks;
using DevelopTool.Common;
using DevelopTool.Dal;
using DevelopTool.Model.DbModels;

namespace DevelopTool.Bll.DbBlls
{
    /// <summary>
    /// 数据库通用操作类
    /// </summary>
    public class DbManagerBll
    {
        /// <summary>
        /// oracle数据库操作类
        /// </summary>
        private OracleDbDal oracleDbDal;

        /// <summary>
        /// 连接信息
        /// </summary>
        public DbInfo dbInfo;


        public DbManagerBll(DbInfo dbInfo)
        {
            this.dbInfo = dbInfo;
            switch(dbInfo.DbType)
            {
                case Model.Enums.DbType.MySql: break;
                case Model.Enums.DbType.SqlServer: break;
                case Model.Enums.DbType.Sqlite: break;
                case Model.Enums.DbType.Oracle:
                    oracleDbDal = new OracleDbDal(new OracleDbBll(dbInfo).GetConnectionString());
                    break;
            }
        }


        /// <summary>
        /// 获取实例名和表名 如果中间有.那么就分割
        /// </summary>
        /// <param name="tableNameStr"></param>
        /// <returns></returns>
        public Tuple<string,string> GetDatabaseNameAndTableName(string tableNameStr)
        {
            if(tableNameStr.Contains("."))
            {
                string[] s = tableNameStr.Split(new char[] { '.' },StringSplitOptions.RemoveEmptyEntries);
                return new Tuple<string,string>(s[0],s[1]);
            }
            else
            {
                return new Tuple<string,string>(string.Empty,tableNameStr);
            }
        }


        /// <summary>
        /// 获取当前用户实例
        /// </summary>
        /// <param name="dbInfo"></param>
        public DataTable GetNowUserDataBaseList()
        {
            DataTable databaseNames = new DataTable();
            switch(dbInfo.DbType)
            {
                case Model.Enums.DbType.MySql: break;
                case Model.Enums.DbType.SqlServer: break;
                case Model.Enums.DbType.Sqlite: break;
                case Model.Enums.DbType.Oracle:
                    databaseNames = oracleDbDal.GetNowUserDataBaseNames();
                    break;
            }
            return databaseNames;
        }


        /// <summary>
        /// 获取所有实例
        /// </summary>
        /// <returns></returns>
        public DataTable GetAllDataBaseList()
        {
            DataTable databaseNames = new DataTable();
            switch(dbInfo.DbType)
            {
                case Model.Enums.DbType.MySql: break;
                case Model.Enums.DbType.SqlServer: break;
                case Model.Enums.DbType.Sqlite: break;
                case Model.Enums.DbType.Oracle:
                    databaseNames = oracleDbDal.GetAllDataBases();
                    break;
            }
            return databaseNames;
        }



        /// <summary>
        /// 获取数据类型集合
        /// </summary>
        /// <returns></returns>
        public List<string> GetDataTypeList(string databaseName = null)
        {
            List<string> list = new List<string>();
            switch(dbInfo.DbType)
            {
                case Model.Enums.DbType.MySql: break;
                case Model.Enums.DbType.SqlServer: break;
                case Model.Enums.DbType.Sqlite: break;
                case Model.Enums.DbType.Oracle:
                    list = oracleDbDal.GetDataTypeList(databaseName);
                    break;
            }
            return list;
        }


        /// <summary>
        /// 获取表集合
        /// </summary>
        /// <param name="databaseName"></param>
        /// <returns></returns>
        public DataTable GetTables(string databaseName = null,string tableName = null)
        {
            DataTable table = new DataTable();
            switch(dbInfo.DbType)
            {
                case Model.Enums.DbType.MySql: break;
                case Model.Enums.DbType.SqlServer: break;
                case Model.Enums.DbType.Sqlite: break;
                case Model.Enums.DbType.Oracle:
                    table = oracleDbDal.GetTables(databaseName,tableName);
                    break;
            }
            return table;
        }

        /// <summary>
        /// 表是否存在
        /// </summary>
        /// <param name="dataBaseName"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public bool ExistTable(string dataBaseName,string tableName)
        {
            bool exist = false;
            switch(dbInfo.DbType)
            {
                case Model.Enums.DbType.MySql: break;
                case Model.Enums.DbType.SqlServer: break;
                case Model.Enums.DbType.Sqlite: break;
                case Model.Enums.DbType.Oracle:
                    exist = oracleDbDal.ExistTable(dataBaseName,tableName);
                    break;
            }

            return exist;
        }

        /// <summary>
        /// 获取表数量
        /// </summary>
        /// <param name="databaseName"></param>
        /// <returns></returns>
        public int GetTableCount(string databaseName = null)
        {
            int count = 0;
            switch(dbInfo.DbType)
            {
                case Model.Enums.DbType.MySql: break;
                case Model.Enums.DbType.SqlServer: break;
                case Model.Enums.DbType.Sqlite: break;
                case Model.Enums.DbType.Oracle:
                    count = oracleDbDal.GetTableCount(databaseName);
                    break;
            }
            return count;
        }


        /// <summary>
        /// 查询表-根据表名集合
        /// </summary>
        /// <param name="tableNames"></param>
        /// <param name="dataBase"></param>
        /// <returns></returns>
        public DataTable GetTablesByTableNames(List<string> tableNames,string dataBase = null)
        {
            DataTable table = new DataTable();
            switch(dbInfo.DbType)
            {
                case Model.Enums.DbType.MySql: break;
                case Model.Enums.DbType.SqlServer: break;
                case Model.Enums.DbType.Sqlite: break;
                case Model.Enums.DbType.Oracle:
                    table = oracleDbDal.GetTablesByTableNames(tableNames,dataBase);
                    break;
            }
            return table;
        }

        /// <summary>
        /// 获取字段集合
        /// </summary>
        /// <param name="dataBase"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public DataTable GetTableColumns(string dataBase,string tableName)
        {
            DataTable table = new DataTable();
            switch(dbInfo.DbType)
            {
                case Model.Enums.DbType.MySql: break;
                case Model.Enums.DbType.SqlServer: break;
                case Model.Enums.DbType.Sqlite: break;
                case Model.Enums.DbType.Oracle:
                    table = oracleDbDal.GetTableColumns(dataBase,tableName);
                    break;
            }
            return table;
        }


        /// <summary>
        /// 查询表字段-根据表名集合
        /// </summary>
        /// <param name="tableNames"></param>
        /// <param name="dataBase"></param>
        /// <returns></returns>
        public DataTable GetTableColumnsByTableNames(List<string> tableNames,string dataBase = null)
        {
            DataTable table = new DataTable();
            switch(dbInfo.DbType)
            {
                case Model.Enums.DbType.MySql: break;
                case Model.Enums.DbType.SqlServer: break;
                case Model.Enums.DbType.Sqlite: break;
                case Model.Enums.DbType.Oracle:
                    table = oracleDbDal.GetTableColumnsByTableNames(tableNames,dataBase);
                    break;
            }
            return table;
        }


        /// <summary>
        /// 测试数据库连接
        /// </summary>
        /// <returns></returns>
        public bool CheckDbConnect()
        {

            switch(dbInfo.DbType)
            {
                case Model.Enums.DbType.MySql: break;
                case Model.Enums.DbType.SqlServer: break;
                case Model.Enums.DbType.Sqlite: break;
                case Model.Enums.DbType.Oracle:
                    return oracleDbDal.TestConnect();
            }
            return false;

        }


        /// <summary>
        /// 执行查询sql
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="startRow"></param>
        /// <param name="endRow"></param>
        /// <returns></returns>
        public DataTable ExecuteQuerySql(string sql,long? startRow = null,long? endRow = null)
        {
            switch(dbInfo.DbType)
            {
                case Model.Enums.DbType.MySql: break;
                case Model.Enums.DbType.SqlServer: break;
                case Model.Enums.DbType.Sqlite: break;
                case Model.Enums.DbType.Oracle:
                    return oracleDbDal.ExecuteQuerySql(sql,startRow,endRow);
            }
            return null;
        }


        /// <summary>
        /// 执行操作sql，增，删，改
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="type">1=sql语句，2=存储过程</param>
        /// <returns>受影响行数</returns>
        public int ExecuteOperationSql(string sql,int type = 1)
        {
            switch(dbInfo.DbType)
            {
                case Model.Enums.DbType.MySql: break;
                case Model.Enums.DbType.SqlServer: break;
                case Model.Enums.DbType.Sqlite: break;
                case Model.Enums.DbType.Oracle:
                    return oracleDbDal.ExecuteOperationSql(sql,type);
            }
            return 0;
        }

        #region 生成实体类

        /// <summary>
        /// 生成实体类
        /// </summary>
        /// <param name="dataBase"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public string GenerateEntity(string dataBase,string tableName)
        {
            DataTable tableTable = GetTables(dataBase,tableName);//查询表信息

            if(tableTable == null || tableTable.Rows.Count <= 0) return string.Empty;

            DataTable columnTable = GetTableColumns(dataBase,tableName);//查询表的所有字段 

            var dataview = columnTable.DefaultView;
            dataview.Sort = "COLUMN_ID ASC";//按数据库中的列顺序排序
            columnTable = dataview.ToTable();

            if(columnTable == null || columnTable.Rows.Count <= 0) return string.Empty;

            StringBuilder tableSb = new StringBuilder();//需要查询的列

            //类注释
            if(tableTable.Rows[0]["COMMENTS"] != null && !string.IsNullOrEmpty(tableTable.Rows[0]["COMMENTS"].ToString()))
            {
                tableSb.AppendLine("\t/// <summary>");
                tableSb.AppendLine($"\t/// {tableTable.Rows[0]["COMMENTS"].ToString().Replace("\n\r"," ").Replace("\n"," ").Replace("\r"," ").Replace(Environment.NewLine," ")}");
                tableSb.AppendLine("\t/// </summary>");
            }

            //类
            tableSb.AppendLine($"\tpublic class {tableTable.Rows[0]["TABLE_NAME"]}");

            StringBuilder columnSb = new StringBuilder();

            foreach(DataRow column in columnTable.Rows)
            {
                //字段名
                string columnName = column["COLUMN_NAME"].ToString();

                #region 字段注释

                //注释
                string columnComments = column["COMMENTS"].ToString().Replace("\n\r"," ").Replace("\n"," ").Replace("\r"," ").Replace(Environment.NewLine," ");//替换字段注释里的换行为。
                columnSb.AppendLine("\t\t/// <summary>");
                columnSb.AppendLine($"\t\t/// {columnComments}");

                //默认值
                if(column["DATA_DEFAULT"] != null && !string.IsNullOrEmpty(column["DATA_DEFAULT"].ToString()))
                {
                    columnSb.AppendLine($"\t\t/// 默认值：{column["DATA_DEFAULT"]}");
                }

                //是否可为空
                columnSb.AppendLine($"\t\t/// 是否可为空：{(column["NULLABLE"].ToString() == "Y" ? "是" : "否")}");

                //数据类型
                columnSb.AppendLine($"\t\t/// 数据类型：{column["DATA_TYPE"]}");

                //数据长度
                columnSb.AppendLine($"\t\t/// 数据长度：{column["DATA_LENGTH"]}");

                columnSb.AppendLine("\t\t/// </summary>");

                #endregion 字段注释

                #region 字段

                int? dataLength = null;
                if(column["DATA_LENGTH"] != null && column["DATA_LENGTH"] is int length) dataLength = length;

                int? dataPrecision = null;
                if(column["DATA_PRECISION"] != null && column["DATA_PRECISION"] is int precision) dataPrecision = precision;

                int? dataScale = null;
                if(column["DATA_SCALE"] != null && column["DATA_SCALE"] is int scale) dataScale = scale;

                string fieldType = MapDataType(column["DATA_TYPE"].ToString(),dataLength,dataPrecision,dataScale);

                columnSb.AppendLine($"\t\tpublic {fieldType} {columnName} {{get; set; }}");
                columnSb.AppendLine("");

                #endregion 字段
            }

            string startBrackets = "\t{" + Environment.NewLine;//开始符号
            string endBrackets = "\t}";//结束符号

            return tableSb.ToString() + startBrackets + columnSb.ToString() + endBrackets;
        }


        /// <summary>
        /// Oracle数据类型映射为C#数据类型文本
        /// </summary>
        /// <param name="oracleDataType"></param>
        /// <param name="dataLength"></param>
        /// <param name="dataPrecision"></param>
        /// <param name="dataScale"></param>
        /// <returns></returns>
        private string MapDataType(string oracleDataType,int? dataLength,int? dataPrecision,int? dataScale)
        {
            // 字符串类型  
            if(oracleDataType.ToUpper().StartsWith("CHAR") || oracleDataType.ToUpper() == "NCHAR" || oracleDataType.ToUpper() == "NVARCHAR2" || oracleDataType.ToUpper() == "VARCHAR2")
            {
                return "string";
            }

            // 数字类型  
            if(oracleDataType.ToUpper() == "NUMBER")
            {
                if(dataPrecision.HasValue && dataScale.HasValue)
                {
                    if(dataPrecision <= 9 && dataScale == 0)
                        return "int"; // 整数  
                    else if(dataPrecision <= 18 && dataScale == 0)
                        return "long"; // 长整数  
                    else if(dataPrecision <= 28 && dataScale <= 28 && dataPrecision >= dataScale)
                        return "decimal"; // 小数  
                    else if(dataScale > 0)
                        return "decimal"; // 带有小数位的小数  
                    else
                        return "decimal";
                }
                else
                {
                    // 没有提供精度或刻度，可能需要一个默认的映射或错误处理  
                    return "decimal"; // 默认映射为小数  
                }
            }

            if(oracleDataType.ToUpper() == "INTEGER")
            {
                return "int"; // 整数
            }

            if(oracleDataType.ToUpper() == "DECIMAL")
            {
                return "decimal"; // 小数
            }

            // 浮点数类型  
            if(oracleDataType.ToUpper() == "FLOAT" || oracleDataType.ToUpper() == "BINARY_FLOAT")
            {
                return "float"; // 浮点数映射为C#的float  
            }

            if(oracleDataType.ToUpper() == "DOUBLE" || oracleDataType.ToUpper() == "BINARY_DOUBLE")
            {
                return "double"; // 双精度浮点数映射为C#的double  
            }

            // 日期和时间类型  
            if(oracleDataType.ToUpper() == "DATE")
            {
                return "DateTime"; // 日期映射为C#的DateTime  
            }

            if(oracleDataType.ToUpper().StartsWith("TIMESTAMP"))
            {
                // 根据具体的时间戳类型（如TIMESTAMP WITH TIME ZONE）
                return "DateTimeOffset"; // 时间戳通常映射为DateTimeOffset  
            }

            // 二进制数据类型  
            if(oracleDataType.ToUpper() == "BLOB")
            {
                return "byte[]"; // BLOB类型映射为byte数组  
            }

            // 大对象类型  
            if(oracleDataType.ToUpper() == "CLOB")
            {
                return "string"; // CLOB类型通常映射为string，但也可以考虑使用其他类型如StreamReader  
            }

            if(oracleDataType.ToUpper() == "NCLOB")
            {
                return "string"; // NCLOB（国家字符集大对象）也通常映射为string  
            }

            // 逻辑类型  
            if(oracleDataType.ToUpper() == "BOOLEAN")
            {
                // Oracle的BOOLEAN类型在C#中没有直接对应，可以使用bool或自定义枚举  
                return "bool"; // 逻辑类型映射为C#的bool  
            }

            // RAW数据类型（用于存储可变长度的原始二进制数据）  
            if(oracleDataType.ToUpper() == "RAW")
            {
                // 根据长度可能需要映射为byte数组或其他类型  
                return "byte[]"; // 假设RAW类型映射为byte数组  
            }

            if(oracleDataType.ToUpper() == "LONG")
            {
                return "byte[]"; // 二进制
            }

            // 其他数据类型...  
            return "object";
        }

        #endregion 生成实体类

        /// <summary>
        /// 根据表名生成指定类型sql
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="sqlType"></param>
        /// <returns></returns>
        public string GenerateSql(string sqlType,string dataBase,string tableName)
        {
            if(sqlType.Equals("select"))
            {
                DataTable columnTable = GetTableColumns(dataBase,tableName);//查询表的所有字段 
                StringBuilder selectColumn = new StringBuilder();//需要查询的列
                foreach(DataRow column in columnTable.Rows)
                {
                    //字段名
                    string columnName = column["COLUMN_NAME"].ToString();
                    //columnName = "\"" + columnName + "\"";//这里将字段名转义下，防止关键字报错

                    //字段注释
                    string columnComments = column["COMMENTS"].ToString().Replace("\n\r"," ").Replace("\n"," ").Replace("\r"," ").Replace(Environment.NewLine," ");//替换字段注释里的换行为。
                    if(!string.IsNullOrEmpty(columnComments))
                        columnComments = "--" + columnComments;

                    selectColumn.AppendLine($"t.{columnName},{columnComments}");
                }
                return $@"SELECT {Environment.NewLine}{selectColumn.Remove(selectColumn.ToString().LastIndexOf(","),1)} FROM {dataBase}.{tableName} t WHERE 1=1";
            }
            else if(sqlType.Equals("update"))
            {
                return $"UPDATE {dataBase}.{tableName} t SET t.修改字段=值 WHERE t.条件字段=值";
            }
            else if(sqlType.Equals("delete"))
            {
                return $"DELETE FROM {dataBase}.{tableName} t WHERE t.条件字段=值";
            }
            else if(sqlType.Equals("insert"))
            {
                DataTable columnTable = GetTableColumns(dataBase,tableName);//查询表的所有字段 
                List<string> columnNameList = new List<string>();
                List<string> columnCommentList = new List<string>();
                foreach(DataRow column in columnTable.Rows)
                {
                    //字段名
                    columnNameList.Add(column["COLUMN_NAME"].ToString());

                    //字段注释
                    columnCommentList.Add(column["COMMENTS"].ToString().Replace("\n\r",string.Empty).Replace("\n",string.Empty).Replace("\r",string.Empty).Replace(Environment.NewLine,string.Empty));//替换字段注释里的换行为空格
                }

                return $@"INSERT INTO {dataBase}.{tableName}{Environment.NewLine} ({string.Join($",{Environment.NewLine}",columnNameList)}){Environment.NewLine} values{Environment.NewLine} ({string.Join($",{Environment.NewLine}",columnCommentList)})";
            }
            else
            {
                return string.Empty;
            }
        }

        /// <summary>
        /// 获取当前用户缺省表空间名
        /// </summary>
        /// <returns></returns>
        public string GetNowUserDatabaseName()
        {
            var table = oracleDbDal.GetNowUserDataBaseNames();
            if(table.Rows.Count <= 0) return "";

            return table.Rows[0][0].ToString();
        }


        /// <summary>
        /// 搜索数据内容
        /// </summary>
        /// <param name="databaseName">实例名</param>
        /// <param name="tableName">表名</param>
        /// <param name="selectedDataTypes">需要搜索的数据库数据类型</param>
        /// <param name="keywords">关键字集合</param>
        /// <param name="matchingMode">搜索模式：模糊、全字匹配</param>
        /// <param name="ignoreCase">是否忽略大小写，忽略=true，不忽略=false</param>
        /// <param name="isCount">是否统计行数</param>
        /// <returns></returns>
        public DataTable SearchDataContent(string databaseName,string tableName,List<string> selectedDataTypes,List<string> keywords,string matchingMode,bool ignoreCase,bool isCount)
        {
            DataTable resultTable = new DataTable("result");
            resultTable.Columns.Add("KEYWORD",typeof(string));
            resultTable.Columns.Add("OWNER",typeof(string));
            resultTable.Columns.Add("NAME",typeof(string));
            resultTable.Columns.Add("TEXT",typeof(string));
            resultTable.Columns.Add("COUNT",typeof(int));
            resultTable.Columns.Add("TYPE",typeof(string));
            resultTable.Columns.Add("COLUMN",typeof(string));

            DataTable columnTable = GetTableColumns(databaseName,tableName);//查询表的所有字段            
            columnTable.DefaultView.RowFilter = "HIDDEN_COLUMN<>'YES'";//筛选非隐藏字段
            columnTable.DefaultView.Sort = "DATA_TYPE DESC";//根据字段数据类型排序，提高些性能
            columnTable = columnTable.DefaultView.ToTable();

            _ = Parallel.ForEach(keywords,new ParallelOptions { MaxDegreeOfParallelism = Environment.ProcessorCount },(keyword,state) =>
            {
                try
                {
                    StringBuilder whereCondition = new StringBuilder();//查询条件
                    StringBuilder selectColumn = new StringBuilder();//需要查询的列

                    HashSet<string> types = new HashSet<string>();//查询的类型

                    //遍历表的字段
                    foreach(DataRow column in columnTable.Rows)
                    {
                        string oracleDataType = column["DATA_TYPE"].ToString();
                        if(!selectedDataTypes.Exists(it => it == oracleDataType)) continue;//跳过未选择的数据类型

                        string supportedDataType = GetSharpTypeByOracleType(oracleDataType);//根据oracle数据类型匹配支持查询的数据类型
                        if(string.IsNullOrEmpty(supportedDataType)) continue;//没找到匹配类型就不查询

                        //字段名
                        string columnName = column["COLUMN_NAME"].ToString();
                        columnName = "\"" + columnName + "\"";//这里将字段名转义下，防止关键字报错

                        //字段注释
                        string columnComments = column["COMMENTS"].ToString().Replace("\n\r"," ").Replace("\n"," ").Replace("\r"," ").Replace(Environment.NewLine," ");//替换字段注释里的换行为空格
                        if(!string.IsNullOrEmpty(columnComments))
                            columnComments = "--" + columnComments;

                        bool isMatchtype = false;//是否匹配到类型

                        //拼装sql查询条件
                        switch(matchingMode)
                        {
                            case "模糊":

                                if(supportedDataType == "字符串")
                                {
                                    if(ignoreCase && StringHelper.HasEnglish(keyword))//搜索关键字包含英文时才转为大写，提高些性能
                                        whereCondition.AppendLine($"INSTR(UPPER(t.{columnName}),'{keyword.ToUpper()}')>0 OR ");
                                    else
                                        whereCondition.AppendLine($"INSTR(t.{columnName},'{keyword}')>0 OR ");

                                    isMatchtype = true;
                                }

                                else if(supportedDataType == "长文本")
                                {
                                    if(ignoreCase && StringHelper.HasEnglish(keyword))//搜索关键字包含英文时才转为大写，提高些性能
                                        whereCondition.AppendLine($"DBMS_LOB.INSTR(UPPER(t.{columnName}),'{keyword.ToUpper()}')>0 OR ");
                                    else
                                        whereCondition.AppendLine($"DBMS_LOB.INSTR(t.{columnName},'{keyword}')>0 OR ");

                                    isMatchtype = true;
                                }

                                else if(supportedDataType == "字节文本")
                                {
                                    whereCondition.AppendLine($"DBMS_LOB.INSTR(t.{columnName},UTL_RAW.CAST_TO_RAW('{keyword}'))>0 OR ");

                                    isMatchtype = true;
                                }

                                else if(supportedDataType == "数值")
                                {
                                    if(int.TryParse(keyword,out _))//尝试将搜索关键字转为数值，尽可能减少查询条件
                                    {
                                        whereCondition.AppendLine($"INSTR(TO_CHAR(t.{columnName}),'{keyword}')>0 OR ");

                                        isMatchtype = true;
                                    }
                                }

                                else if(supportedDataType == "日期")
                                {
                                    if(DateTime.TryParse(keyword,out _))//尝试将搜索关键字转为日期，尽可能减少查询条件
                                    {
                                        whereCondition.AppendLine($"INSTR(TO_CHAR(t.{columnName},'yyyy/mm/dd HH24:mi:ss'),'{keyword}')>0 OR ");

                                        isMatchtype = true;
                                    }
                                }
                                break;
                            case "全字匹配":

                                if(supportedDataType == "字符串" || supportedDataType == "长文本")
                                {
                                    if(ignoreCase && StringHelper.HasEnglish(keyword))//搜索关键字包含英文时才转为大写，提高些性能
                                        whereCondition.AppendLine($"UPPER(t.{columnName})='{keyword.ToUpper()}' OR ");
                                    else
                                        whereCondition.AppendLine($"t.{columnName}='{keyword}' OR ");

                                    isMatchtype = true;
                                }

                                else if(supportedDataType == "字节文本")
                                {
                                    whereCondition.AppendLine($"t.{columnName}=UTL_RAW.CAST_TO_RAW('{keyword}') OR ");

                                    isMatchtype = true;
                                }

                                else if(supportedDataType == "数值")
                                {
                                    if(int.TryParse(keyword,out _))//尝试将搜索关键字转为数字
                                    {
                                        whereCondition.AppendLine($"t.{columnName}={keyword} OR ");

                                        isMatchtype = true;
                                    }
                                }

                                else if(supportedDataType == "日期")
                                {
                                    if(DateTime.TryParse(keyword,out _))//尝试将搜索关键字转为日期
                                    {
                                        whereCondition.AppendLine($"t.{columnName}=TO_DATE('{keyword}','yyyy/mm/dd HH24:mi:ss') OR ");

                                        isMatchtype = true;
                                    }
                                }

                                break;
                        }

                        if(isMatchtype)
                        {
                            selectColumn.AppendLine($"t.{columnName},{columnComments}");
                            types.Add(oracleDataType);
                        }
                    }

                    if(whereCondition.Length > 0 && selectColumn.Length > 0)
                    {
                        string existsSql = string.Empty;//查询记录是否存在sql

                        if(isCount)//是否统计行数
                        {
                            //统计行数（虽然效率比下面的低一些，但是某些情况下还需要用）
                            existsSql = $"SELECT COUNT(*) FROM {databaseName}.{tableName} t WHERE @whereCondition@";
                        }
                        else
                        {
                            //不统计行数，只判断是否存在
                            existsSql = $"SELECT 1 FROM DUAL WHERE EXISTS (SELECT 1 FROM {databaseName}.{tableName} t WHERE @whereCondition@)";
                        }

                        string whereConditionStr = whereCondition.ToString();//查询条件 
                        whereConditionStr= whereConditionStr.RemoveLastStr(" OR ");//去除最后一个OR
                        existsSql = existsSql.Replace("@whereCondition@",whereConditionStr);//拼装查询sql

                        int count = oracleDbDal.GetCount(existsSql);//查询

                        if(count > 0)
                        {
                            DataRow row = resultTable.NewRow();
                            row["KEYWORD"] = keyword;
                            row["OWNER"] = databaseName;
                            row["NAME"] = tableName;
                            row["TEXT"] = $"SELECT {selectColumn.Remove(selectColumn.ToString().LastIndexOf(","),1)} FROM {databaseName}.{tableName} t WHERE {whereConditionStr}";
                            row["COUNT"] = count;
                            row["TYPE"] = string.Join(",",types);
                            row["COLUMN"] = "";
                            resultTable.Rows.Add(row);
                        }
                    }

                }
                catch(Exception ex)
                {
                    LogHelper.WriteErrLog(ex);
                }
            });

            return resultTable;
        }                


        private string GetSharpTypeByOracleType(string oracleType)
        {
            switch(oracleType)
            {
                case "CHAR":
                case "NCHAR":
                case "NVARCHAR2":
                case "VARCHAR2":
                case "ROWID":
                case "UROWID":
                    return "字符串";
                case "FLOAT":
                case "INTEGER":
                case "NUMBER":
                case "DECIMAL":
                    return "数值";
                case "TIMESTAMP(3)":
                case "TIMESTAMP(4)":
                case "TIMESTAMP(6)":
                case "TIMESTAMP":
                case "DATE":
                    return "日期";
                //case "LONG":
                //    return "过时长文本";
                case "BLOB":
                    return "字节文本";
                case "CLOB":
                case "NCLOB":
                    return "长文本";
                default:
                    return string.Empty;
            }
        }


        /// <summary>
        /// 搜索对象内容
        /// </summary>
        /// <param name="keywords">关键字集合</param>
        /// <param name="sType">资源类型 table view function 等等...</param>
        /// <param name="matchingMode">匹配方式 0=模糊，1=全字匹配</param>
        /// <param name="ignoreCase">是否忽略大小写</param>
        /// <returns></returns>
        public DataTable SearchTypeContent(string databaseName,string sType,List<string> keywords,string matchingMode,bool ignoreCase)
        {
            DataTable resultTable = new DataTable("result");
            resultTable.Columns.Add("KEYWORD",typeof(string));
            resultTable.Columns.Add("NAME",typeof(string));
            resultTable.Columns.Add("TEXT",typeof(string));
            resultTable.Columns.Add("COUNT",typeof(int));
            resultTable.Columns.Add("TYPE",typeof(string));
            resultTable.Columns.Add("COLUMN",typeof(string));

            Dictionary<string,string> typeColumns = new Dictionary<string,string>();
            string nameColumn = string.Empty;

            DataTable dataTable = new DataTable();
            switch(sType)
            {
                case "表":
                    dataTable = oracleDbDal.GetTables(databaseName);
                    typeColumns = new Dictionary<string,string>()
                    {
                        {"TABLE_NAME","表名"},
                        {"COMMENTS","表注释"},
                    };
                    nameColumn = "TABLE_NAME";
                    break;
                case "视图":
                    dataTable = oracleDbDal.GetViews(databaseName);
                    typeColumns = new Dictionary<string,string>()
                    {
                        {"VIEW_NAME","视图名"},
                        {"TEXT","视图Sql"},
                    };
                    nameColumn = "VIEW_NAME";
                    break;
                case "表字段":
                    dataTable = oracleDbDal.GetTableColumns(databaseName);
                    typeColumns = new Dictionary<string,string>()
                    {
                        {"COLUMN_NAME","字段名"},
                        {"COMMENTS","字段注释"},
                    };
                    nameColumn = "TABLE_NAME";
                    break;
                case "视图字段":
                    dataTable = oracleDbDal.GetViewColumns(databaseName);
                    typeColumns = new Dictionary<string,string>()
                    {
                        {"COLUMN_NAME","字段名"},
                        {"COMMENTS","字段注释"},
                    };
                    nameColumn = "TABLE_NAME";
                    break;
                case "函数":
                    dataTable = oracleDbDal.GetFuncs(databaseName);
                    typeColumns = new Dictionary<string,string>()
                    {
                        {"OBJECT_NAME","函数名"},
                        {"TEXT","函数sql"},
                    };
                    nameColumn = "OBJECT_NAME";
                    break;
                case "存储过程":
                    dataTable = oracleDbDal.GetProcedures(databaseName);
                    typeColumns = new Dictionary<string,string>()
                    {
                        {"OBJECT_NAME","存储过程名"},
                        {"TEXT","存储过程sql"},
                    };
                    nameColumn = "OBJECT_NAME";
                    break;
                case "触发器":
                    dataTable = oracleDbDal.GetTriggers(databaseName);
                    typeColumns = new Dictionary<string,string>()
                    {
                        {"TRIGGER_NAME","触发器名"},
                        {"TRIGGERING_EVENT","触发事件"},
                        {"DESCRIPTION","描述"},
                        {"TRIGGER_BODY","语句"},
                        {"WHEN_CLAUSE","WHEN_子句"},
                    };
                    nameColumn = "TRIGGER_NAME";
                    break;
                case "job":
                    dataTable = oracleDbDal.GetJobs(databaseName);
                    typeColumns = new Dictionary<string,string>()
                    {
                        {"JOB","job编号"},
                        {"WHAT","正文"},
                        {"NLS_ENV","会话参数"},
                        {"MISC_ENV","其他会话参数"},
                    };
                    nameColumn = "JOB";
                    break;
                case "同义词":
                    dataTable = oracleDbDal.GetSynonyms(databaseName);
                    typeColumns = new Dictionary<string,string>()
                    {
                        {"SYNONYM_NAME","同义词名"},
                        {"DB_LINK","数据库链接"},
                    };
                    nameColumn = "SYNONYM_NAME";
                    break;
                case "用户":
                    dataTable = oracleDbDal.GetUsers();
                    typeColumns = new Dictionary<string,string>()
                    {
                        {"USERNAME","用户名"},
                    };
                    nameColumn = "USERNAME";
                    break;
                case "包":
                    dataTable = oracleDbDal.GetPackages(databaseName);
                    typeColumns = new Dictionary<string,string>()
                    {
                        {"OBJECT_NAME","包名"},
                        {"PACKAGE_TEXT","包Sql"},
                        {"PACKAGEBODY_TEXT","包体Sql"},
                    };
                    nameColumn = "OBJECT_NAME";
                    break;
                case "序列":
                    dataTable = oracleDbDal.GetSequences(databaseName);
                    typeColumns = new Dictionary<string,string>()
                    {
                        {"SEQUENCE_NAME","序列名"},
                    };
                    nameColumn = "SEQUENCE_NAME";
                    break;
            }

            foreach(DataRow dataRow in dataTable.Rows)
            {
                foreach(string column in typeColumns.Keys)
                {
                    if(dataRow[column] == null || string.IsNullOrEmpty(dataRow[column].ToString())) continue;

                    string value = dataRow[column].ToString();
                    string name = dataRow[nameColumn].ToString();

                    foreach(string keyword in keywords)
                    {
                        bool exists = false;//是否匹配到
                        switch(matchingMode)
                        {
                            //模糊匹配
                            case "模糊":
                                if(ignoreCase)//忽略大小写
                                {
                                    exists = value.IndexOf(keyword,StringComparison.OrdinalIgnoreCase) > -1;
                                }
                                else//不忽略大小写
                                {
                                    exists = value.IndexOf(keyword,StringComparison.Ordinal) > -1;
                                }
                                break;
                            //全字匹配
                            case "全字匹配":
                                if(ignoreCase)//忽略大小写
                                {
                                    exists = value.ToUpper() == keyword.ToUpper();
                                }
                                else//不忽略大小写
                                {
                                    exists = value == keyword;
                                }
                                break;
                        }
                        if(exists)
                        {
                            DataRow row = resultTable.NewRow();
                            row["KEYWORD"] = keyword;
                            row["NAME"] = name;
                            row["TEXT"] = value;
                            row["COUNT"] = 0;
                            row["TYPE"] = sType;
                            row["COLUMN"] = typeColumns[column];
                            resultTable.Rows.Add(row);
                        }
                    }
                }
            }
            return resultTable;
        }


        /// <summary>
        /// 查询数据库锁表数据
        /// </summary>
        /// <param name="dbInfo"></param>
        public DataTable GetDbLockData()
        {
            DataTable table = new DataTable();
            switch(dbInfo.DbType)
            {
                case Model.Enums.DbType.MySql: break;
                case Model.Enums.DbType.SqlServer: break;
                case Model.Enums.DbType.Sqlite: break;
                case Model.Enums.DbType.Oracle:
                    table = oracleDbDal.GetDbLockData();
                    break;
            }
            return table;
        }

    }
}
